﻿//=================================
// 数据库对象操作类
// author:郑金泉
// created:2010-12-03
//=================================
using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

using Obsidian.Utils;
using Obsidian.Edm;

namespace Obsidian.Data.Sql.OleDbClient
{
    public class OleDbHandler : DbHandler, IDbHandler
    {
        private OleDbExecutor executor = new OleDbExecutor();

        public OleDbHandler() { }

        public OleDbHandler(IModel model)
        {
            this.OperateModel = model;
        }

        private void PrepareExecutor()
        {
            executor.ConnectionString = this.OperateModel.GetDataBase().ConnectionString;
        }

        #region Insert

        public object Insert()
        {
            OleDbParameter[] oleDbParams;
            string sqlText = this.BuildInsertCmdText(out oleDbParams);
            this.PrepareExecutor();
            object val = null;
            OleDbTransaction transaction;
            OleDbCommand command = null;
            try
            {
                command = executor.BeginTransaction(CommandType.Text, oleDbParams, sqlText, out transaction);
                int n = command.ExecuteNonQuery();
                if (n > 0)
                {
                    command.CommandText = @"SELECT @@IDENTITY";
                    val = command.ExecuteScalar();
                }
                transaction.Commit();
            }
            catch { throw; }
            finally
            {
                if (command != null)
                    command.Connection.Close();
            }
            return val;
        }

        private string BuildInsertCmdText(out OleDbParameter[] oleDbParams)
        {
            List<OleDbParameter> paramList = new List<OleDbParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO [")
                .Append(this.OperateModel.DbTableName)
                .Append("] (");
            foreach (OperateField of in this.fieldList)
            {
                DbField dbf = of.ModelField.GetDbField();
                sb.Append("[").Append(dbf.Name).Append("],");
            }
            sb.Remove(sb.Length - 1, 1).Append(") VALUES (");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string paramName = "@P_" + n.ToString();
                sb.Append(paramName).Append(",");
                OleDbParameter prm;
                if (of.ModelField.Type == FieldType.DateTime && !of.Value.Equals(null))
                    prm = new OleDbParameter(paramName, ((DateTime)of.Value).ToString("yyyy-MM-dd HH:mm:ss"));
                else
                    prm = new OleDbParameter(paramName, of.DbValue);
                paramList.Add(prm);
                n++;
            }
            sb.Remove(sb.Length - 1, 1).Append(")");
            oleDbParams = paramList.ToArray();
            return sb.ToString();
        }

        #endregion

        #region Update

        public int Update()
        {
            OleDbParameter[] oleDbParams;
            string sqlText = this.BuildUpdateCmdText(out oleDbParams);
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText, oleDbParams);
            return n;
        }

        private string BuildUpdateCmdText(out OleDbParameter[] oleDbParams)
        {
            List<OleDbParameter> paramList = new List<OleDbParameter>();
            StringBuilder sb = new StringBuilder();
            sb.Append("UPDATE [")
                .Append(this.OperateModel.DbTableName)
                .Append("] SET ");
            int n = 0;
            foreach (OperateField of in this.fieldList)
            {
                string paramName = "@P_" + n.ToString();
                sb.Append("[").Append(of.ModelField.GetDbField().Name).Append("]=")
                    .Append(paramName).Append(",");
                OleDbParameter prm;
                if (of.ModelField.Type == FieldType.DateTime && !of.Value.Equals(null))
                    prm = new OleDbParameter(paramName, ((DateTime)of.Value).ToString("yyyy-MM-dd HH:mm:ss"));
                else
                    prm = new OleDbParameter(paramName, of.DbValue);
                paramList.Add(prm);
                n++;
            }
            sb.Remove(sb.Length - 1, 1);

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");

            oleDbParams = paramList.ToArray();
            return sb.ToString();
        }

        #endregion

        #region Select

        public bool SelectFirstTo(params IModel[] models)
        {
            DataReader dr = this.Select();
            if (dr.Read())
            {
                dr.ReadTo(models);
                dr.Close();
                return true;
            }
            else
            {
                return false;
            }
        }

        public DataReader Select()
        {
            string sqlText = this.BuildSelectCmdText(null);
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);
            return new DataReader(dr);
        }

        public DataReader Select(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.Select(qry, out listAttr);
        }

        public DataReader Select(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            CheckQueryInfo(qry);

            int itemsCount = this.SelectItemsCount();
            int pagesCount = MathUtil.PagesCount(qry.PageSize.Value, itemsCount);
            listAttr = new ListAttrInfo();
            listAttr.ItemsCount.Set(itemsCount);
            listAttr.PageSize.Set(qry.PageSize.Value);
            listAttr.PagesCount.Set(pagesCount);
            int pageNo = qry.PageNo.Value;
            if (pageNo > pagesCount)
                pageNo = pagesCount;
            listAttr.PageNo.Set(pageNo);

            DataReader dr = this.Pagination(qry, listAttr);

            return dr;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="qry"></param>
        /// <param name="listAttr"></param>
        /// <returns></returns>
        private DataReader Pagination(QueryInfo qry, ListAttrInfo listAttr)
        {
            string sqlText = null;
            if (qry.IsPaging.isFalse() || qry.PageNo.Value == 1)
            {
                sqlText = this.BuildSelectCmdText(qry);
            }
            else
            {
                sqlText = this.BuildPaginationCmdText(qry, listAttr);
            }
            this.PrepareExecutor();
            IDataReader dr = executor.ExecuteReader(sqlText);

            return new DataReader(dr);
        }

        /// <summary>
        /// 查询总数量
        /// </summary>
        /// <returns></returns>
        public int SelectItemsCount()
        {
            string joinText = this.BuildJoinText();
            string whereText = null;
            if (this.whereCondition.SubConditions.Count > 0)
                whereText = this.whereCondition.BuildSqlText();

            StringBuilder sb = new StringBuilder();
            sb.Append("\r\n").Append(" SELECT COUNT(*) FROM ")
                .Append(StringUtil.Repetition("(", this.dictJoinClauses.Count))
                .Append("[")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("]");
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            if (!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append(" WHERE ").Append(whereText);
            this.PrepareExecutor();
            string sqlText = sb.ToString();
            return Convert.ToInt32(executor.ExecuteScalar(sqlText));
        }

        public int SelectItemsCount(QueryInfo qry)
        {
            ListAttrInfo listAttr = null;
            return this.SelectItemsCount(qry, out listAttr);
        }

        public int SelectItemsCount(QueryInfo qry, out ListAttrInfo listAttr)
        {
            if (qry == null)
                throw new Exception("qry参数不能为空!");

            CheckQueryInfo(qry);

            int itemsCount = this.SelectItemsCount();
            int pagesCount = MathUtil.PagesCount(qry.PageSize.Value, itemsCount);
            listAttr = new ListAttrInfo();
            listAttr.ItemsCount.Set(itemsCount);
            listAttr.PageSize.Set(qry.PageSize.Value);
            listAttr.PagesCount.Set(pagesCount);
            int pageNo = qry.PageNo.Value;
            if (pageNo > pagesCount)
                pageNo = pagesCount;
            listAttr.PageNo.Set(pageNo);
            return itemsCount;
        }

        /// <summary>
        /// 构造分页查询语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildPaginationCmdText(QueryInfo qry, ListAttrInfo listAttr)
        {
            string pkTablePrefixName = this.OperateModel.PimaryKey.GetDbField().TablePrefixName;
            string joinText = this.BuildJoinText();
            string whereText = null;
            if (this.whereCondition.SubConditions.Count > 0)
                whereText = this.whereCondition.BuildSqlText();

            string orderByText = this.BuildOrderByText();
            if (String.IsNullOrEmpty(orderByText))
                throw new Exception("至少要有一个排序字段!");

            int tmpTop = qry.PageSize.Value * qry.PageNo.Value;
            int pageSize = qry.PageSize.Value;
            int count = listAttr.ItemsCount.Value;
            if (count < tmpTop)
            {
                tmpTop = count;
                pageSize = count % pageSize;
            }

            StringBuilder sb = new StringBuilder();

            sb.Append("\r\n").Append(" SELECT * FROM (SELECT TOP ").Append(pageSize);
            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                sb.Append("\r\n").Append(" [").Append(dbField.Name).Append("] AS ").Append(dbField.QueryAlias)
                    .Append((n == this.fieldList.Count - 1) ? " " : ",");
                n++;
            }
            sb.Append("\r\n").Append(" FROM [").Append(this.OperateModel.GetDbTable().Name);
            sb.Append("\r\n").Append("] WHERE ").Append(pkTablePrefixName ).Append(" IN (");
            sb.Append("\r\n").Append(" SELECT TOP ").Append(tmpTop).Append(" ").Append(pkTablePrefixName);
            sb.Append("\r\n").Append(" FROM ");
            sb.Append(StringUtil.Repetition("(", this.dictJoinClauses.Count));
            sb.Append("[").Append(this.OperateModel.GetDbTable().Name).Append("]");
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            if (!String.IsNullOrEmpty(whereText))
                sb.Append("\r\n").Append(" WHERE ").Append(whereText);
            sb.Append("\r\n").Append(" ORDER BY ").Append(orderByText);
            sb.Append("\r\n").Append(" ) ORDER BY ")
                .Append(orderByText.Replace("ASC", "ASC_DESC").Replace(" DESC", " ASC").Replace("ASC_", ""));

            StringBuilder sbOrder = new StringBuilder();
            foreach (OrderByClause clause in this.orderByList)
            {
                sbOrder.Append("tb__temp.").Append(clause.Field.GetDbField().QueryAlias).Append(" ")
                    .Append(clause.Type == OrderType.ASC ? "ASC " : "DESC ");
            }
            sb.Append("\r\n").Append(") AS tb__temp ORDER BY ").Append(sbOrder.ToString());
            return sb.ToString();
        }

        /// <summary>
        /// 构造查询语句
        /// </summary>
        /// <param name="qry"></param>
        /// <returns></returns>
        private string BuildSelectCmdText(QueryInfo qry)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ");

            if (qry != null && qry.IsPaging.isTrue() && qry.PageNo.Value == 1)
                sb.Append("TOP ").Append(qry.PageSize.Value.ToString()).Append(" ");

            int n = 0;
            foreach (OperateField field in this.fieldList)
            {
                DbField dbField = field.ModelField.GetDbField();
                if (dbField != null)
                {
                    sb.Append("\r\n").Append(dbField.TablePrefixName).Append(" AS ").Append(dbField.QueryAlias)
                        .Append((n == this.fieldList.Count - 1) ? "" : ",");
                    n++;
                }
            }
            sb.Append("\r\n").Append(" FROM ")
                .Append(StringUtil.Repetition("(", this.dictJoinClauses.Count))
                .Append("[")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("] ");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
                sb.Append("\r\n").Append(joinText);
            
            if(this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());

            string orderByStr = this.BuildOrderByText();
            if (!String.IsNullOrEmpty(orderByStr))
                sb.Append("\r\n").Append(" ORDER BY ").Append(orderByStr);
                
            return sb.ToString();
        }

        #endregion

        #region Delete

        public int Delete()
        {
            string sqlText = this.BuildDeleteCmdText();
            this.PrepareExecutor();
            int n = executor.ExecuteNonQuery(sqlText);
            return n;
        }

        private string BuildDeleteCmdText()
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("DELETE FROM [")
                .Append(this.OperateModel.GetDbTable().Name)
                .Append("]");

            string joinText = this.BuildJoinText();
            if (!String.IsNullOrEmpty(joinText))
            {
                sb.Append(" FROM ")
                    .Append(StringUtil.Repetition("(", this.dictJoinClauses.Count))
                    .Append("[")
                    .Append(this.OperateModel.GetDbTable().Name)
                    .Append("]");
                sb.Append("\r\n").Append(joinText);
            }

            if (this.whereCondition.SubConditions.Count > 0)
                sb.Append("\r\n").Append(" WHERE ").Append(this.whereCondition.BuildSqlText());
            else
                throw new Exception("警告！为确保安全，更新操作where条件不能为空！");
            return sb.ToString();
        }

        #endregion

        #region

        private string BuildJoinText()
        {
            if (this.dictJoinClauses.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (string key in this.dictJoinClauses.Keys)
                {
                    JoinClause joinClause = dictJoinClauses[key];
                    sb.Append(joinClause.BuildSqlText()).Append(")");
                }
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        public IDbHandler OrderBy(IModelField field)
        {
            OrderByClause clause = new OrderByClause(field, OrderType.ASC);
            this.orderByList.Add(clause);
            return this;
        }

        public IDbHandler OrderBy(IModelField field, OrderType type)
        {
            OrderByClause clause = new OrderByClause(field, type);
            this.orderByList.Add(clause);
            return this;
        }

        private string BuildOrderByText()
        {
            if (this.orderByList.Count > 0)
            {
                StringBuilder sb = new StringBuilder();
                foreach (OrderByClause clause in this.orderByList)
                    sb.Append(clause.BuildSqlText()).Append(",");
                sb.Remove(sb.Length - 1, 1);
                return sb.ToString();
            }
            else
            {
                return null;
            }
        }

        #endregion

    }
}
